package com.sherry.uitils.dbutils;

import com.sherry.uitils.ConfigUtil;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Map;

/**
 * Created by swl on 2021/5/31.
 */
public class SqlUtil {

    private static Connection getDbConnection(String sql){
        Map<String, DBConnectionInfo> dbInfoMap = ConfigUtil.getDbConfigInfo();
        Connection connection = null;

        for (String dbServerName : dbInfoMap.keySet()){
            if (!sql.contains(dbServerName)){
                continue;
            }
            if (sql.contains(dbServerName)){
                DBConnectionInfo dbInfo = dbInfoMap.get(dbServerName);
                //连接数据库
                try {
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    if (connection == null){
                        connection = DriverManager.getConnection(dbInfo.getDbUrl(),dbInfo.getUserName(),dbInfo.getPassword());
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
            }
        }
        return connection;

    }

    public static <T> T execSqlSingle(String sql,Class<T> clazz){
        Connection connection = getDbConnection(sql);
        Statement statement = null;
        ResultSet resultSet = null;
        T object = null;
        try {
            object = clazz.newInstance();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            // 返回单个对象，校验查出来的数据是否是单个
            resultSet.last();
            if (resultSet.getRow() >1 ){
//                throw new TooManyResultsException("Expected one result (or null) to be returned by selectOne(), but found: " + resultSet.getRow());
            }

            // 返回第一个
            resultSet.beforeFirst();
            // 没有数据返回null
            if (!resultSet.next()) {
                return null;
            }
            // 返回第一个
            resultSet.beforeFirst();

            String[] colNames = getColNames(resultSet);
            while (resultSet.next()) {
                getObject(resultSet, object, colNames, clazz);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
            releaseDb(connection,statement,resultSet);
        }
        return object;

    }

    /**
     * 根据结果集获取查询结果的别名
     * @param rs
     * @return
     * @throws SQLException
     */
    private static String[] getColNames(ResultSet rs) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取查询的列数
        int count = rsmd.getColumnCount();
        String[] colNames = new String[count];
        for (int i = 1; i <= count; i++) {
            //获取查询类的别名
            colNames[i - 1] = rsmd.getColumnLabel(i);
        }
        return colNames;
    }

    private static <T> void getObject(ResultSet rs, T object, String[] colNames, Class<T> clazz) {
        try {

            Field[] fields = clazz.getDeclaredFields();
            for (int i = 0 ; i< colNames.length;i++){
                String colName = colNames[i];
                Field field = fields[i];
                String fieldName = field.getName();
                String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
                Class<?> type = field.getType();
                Method method = clazz.getMethod(methodName, type);
                if (rs.getObject(colName) == null) {
                    continue;
                }
                String colString = rs.getObject(colName).toString();
                if (type.equals(Byte.class)) {
                    Byte value = Byte.valueOf(colString);
                    method.invoke(object, value);
                } else if (type.equals(Short.class)) {
                    Short value = Short.valueOf(colString);
                    method.invoke(object, value);
                } else if (type.equals(Integer.class)) {
                    Integer value = Integer.valueOf(colString);
                    method.invoke(object, value);
                } else if (type.equals(Double.class)) {
                    Double value = Double.valueOf(colString);
                    method.invoke(object, value);
                } else if (type.equals(Long.class)) {
                    Long value = Long.valueOf(colString);
                    method.invoke(object, value);
                } else if (type.equals(String.class)) {
                    String value = String.valueOf(colString);
                    method.invoke(object, value);
                } else if (type.equals(java.util.Date.class)) {
                    java.util.Date value = (java.util.Date) rs.getObject(colName);
                    method.invoke(object, new java.util.Date(value.getTime()));
                } else if (type.equals(java.sql.Date.class)) {
                    java.sql.Date value = (java.sql.Date) (rs.getObject(colName));
                    method.invoke(object, new java.util.Date(value.getTime()));
                } else if (type.equals(BigDecimal.class)) {
                    BigDecimal value = new BigDecimal(colString);
                    method.invoke(object, value);
                } else {
                    method.invoke(object, rs.getObject(colName));
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void releaseDb(Connection connection, Statement statement, ResultSet resultSet){

        try {
            if (resultSet != null){
                resultSet.close();
            }
            if (statement != null){
                statement.close();
            }
            if (connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
